/*
 * @Author: 王雁鹏
 * @Date: 2022-12-29 22:26:35
 * @LastEditTime: 2023-07-27 19:47:20
 * @LastEditors: Please set LastEditors
 * @Description: 
 * @FilePath: \expressf:\web-vue\bs-nodejs-background-code\mysql\index.js
 * -------------------------------------
 */
const utils = require("../utils/router");
let { DBMap, MapDB } = utils
const mysql = require("mysql");
let KV = (db, select) => {
    let params = DBMap(db, select)
    let obj = {};
    console.log(params)
    for (let i in params) {
        obj['key'] = i
        obj['value'] = params[i]
    }
    return obj
}
class Mysql {
    constructor() {
        this.connection = {}
        this.createConnection()
        // this.connection.connect()
        // let userId = 3
        // let columns = ['o_prod', 'o_user', 'prod_name']
        // let query = this.connection.query("select ?? from ?? limit ?,?", [columns, 'vw_order', 0, 4], (a, b, c) => {
        // })
    }
    createConnection() {
        let localhost = {
            host: 'localhost',
            user: 'wyp216',
            password: 'wyp216216',
            database: 'mine'
        }
        let tiequan = {
            host: '162.14.80.40',
            user: 'root',
            password: 'Admin@123@qwe',
            database: 'warehouse'
        }
        let xwj = {
            host: '162.14.64.133',
            user: 'root',
            password: 'Wyp13623425200!',
            database: 'warehouse'
        }
        let connection = tiequan
        // let connection = xwj
        this.connection = mysql.createConnection(connection)
    }
    // 等于
    EQ({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}='${value}'`
    }
    // 不等于
    NE({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}!='${value}'`
    }
    // 大于
    GT({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}>${value}`
    }
    // 小于
    LT({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}<${value}`
    }
    // 大于等于
    GE({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}>=${value}`
    }
    // 小于等于
    LE({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key}<=${value}`
    }
    // 模糊查询
    LIKE({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key} LIKE '%${value}%'`
    }
    // IN字符
    IN({ db, select }) {
        let { key, value } = KV(db, select);
        if (!value) return ""
        return `${key} IN (${value})`
    }
    // 单例声明
    static getInstance() {
        if (!this.instance) {
            this.instance = new Mysql()
        }
        return this.instance
    }
    // 数据连接
    connect(name, sql, fn) {
        this.createConnection()
        this.connection.connect()
        this.connection[name](sql, (err, rows, fields) => {
            if (err) throw err
            return fn(rows, fields)
        })
        this.connection.end()
    }
    query(sql, fn) {
        return this.connect('query', sql, fn)
    }
    only(key, form, value, fn) {
        let sql = `SELECT ${key} FROM ${form} where ${key}='${value}' group by ${key} having count(${key})>1`
        this.query(sql, (row, field) => {
            fn(!(row.length > 0))
        })
        return
    }
    selectAll(name, fn) {
        let sql = `SELECT * FROM ${name}`
        this.query(sql, (row, field) => {
            let arr = this.switch(name, row)
            fn(arr, field)
        })
    }
    setData({
        db = "wh_menu",
        id,
        query,
        condition = {}
    }, fn) {
        let obj = DBMap(db, condition)
        let sql = () => {
            if (id) {
                let update = this.update(obj, db)
                let selectId = `where id=${id}`
                return `${update} ${selectId}`
            } else if (query) {
                let update = this.update(obj, db)
                let selectId = `where ` + query
                return `${update} ${selectId}`
            } else {
                return this.insert(db, obj)
            }
        }

        this.query(sql(), (row, field) => {
            if (fn) {
                fn(row, field)
            }
        })
    }
    // 查询
    select({
        db = "wh_menu",
        select = [],
        pageSize,
        pageNum
    }, fn) {
        let selectStr = select.length > 0 ? `where ${select.join(" AND ")}` : ""
        let limit = () => {
            if (pageNum && pageSize) {
                let startIndex = pageNum == 1 ? (pageNum - 1) : (pageSize * (pageNum - 1))
                return `limit ${startIndex},${pageSize}`
            }
            return ""
        }
        let sql = `SELECT * FROM ${db}  ${selectStr} ${limit()}`
        let total = `SELECT count(*) as total FROM ${db}  ${selectStr}`
        console.log({
            sql, total
        })
        return this.query(total, (res) => {
            this.query(sql, (row, field) => {
                if (fn) {
                    let arr = this.switch(db, row)
                    fn({
                        total: res[0].total,
                        list: arr
                    }, field)
                }
            })
        })

    }
    switch(name, row) {
        let arr = row.map(item => {
            // let obj = {}
            // for (let key in item) {
            //     let arr = key.split('_')
            //     if (arr.length > 1) {
            //         obj[arr[1]] = item[key]
            //     }
            //     if (arr.length == 1) {
            //         obj[arr[0]] = item[key]
            //     }
            // }
            return MapDB(name, item)
        })
        return arr
    }
    select1(select, form) {
        return `SELECT ${select} FROM ${form}`
    }
    insert(select, data) {
        let keys = []
        let values = []
        for (let i in data) {
            keys.push(i)
            values.push(`'${data[i]}'`)
        }
        return `INSERT INTO ${select} (${keys.join(',')}) VALUES (${values.join(',')})`
    }
    update(select, form) {
        let sql = []
        for (let i in select) {
            sql.push(`${i}='${select[i]}'`)
        }
        return `UPDATE ${form} SET ${sql.join(',')}`
    }
    delete({
        db,
        select,
    }, fn) {
        let selectStr = select.length > 0 ? `where ${select.join(" AND ")}` : ""
        let sql = `DELETE FROM ${db} ${selectStr}`
        this.query(sql, (row, field) => {
            if (fn) {
                fn(row, field)
            }
        })
    }
    where(sql) {
        return sql
    }
    limit(index = 0, num = 15) {
        return `limit ${index == 0 ? index : index * num - 1},${num} `
    }
    Params(obj) {
        let select = []
        for (let i in obj) {
            if (obj[i]) {
                select.push(obj[i])
            }
        }
        return select
    }
}
// select * from wh_produce 查询所有商品
// select * from wh_category 查询所有类目
// insert into wh_menu (m_name,m_code,m_sort,m_status,m_details) values ('铁拳菜单','1','1','1','详情内容'); 添加菜单内容
module.exports = Mysql.getInstance()

